package com.situ.dao.impl;

import com.situ.common.Global;
import com.situ.dao.StudentDao;
import com.situ.pojo.Student;
import com.situ.pojo.search.StudentDTO;
import com.situ.util.Pagination;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.StringUtils;

import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
//数据仓库  操作数据库
public class StudentDaoImpl implements StudentDao {
    private BeanPropertyRowMapper<Student> rowMapper = new BeanPropertyRowMapper<>(Student.class);

    @Override
    public List<Student> findAll(Pagination pagination, StudentDTO studentDTO) {
        JdbcTemplate jdbcTemplate = Global.getJdbcTemplate();
        List<Object> args=new ArrayList<>();

        String select="select id,name,sex,phone,email,weixin,qq,card_id,pinyin,birthday from t_hr_humen";
        StringBuilder sb = new StringBuilder();
        sb.append(" where 1=1");
        if (StringUtils.hasText(studentDTO.getName())){
            sb.append(" and name like ?");
            args.add("%"+studentDTO.getName()+"%");
        }
        if (StringUtils.hasText(studentDTO.getCardId())){
            sb.append(" and card_id = ?");
            args.add(studentDTO.getCardId());
        }
        if (StringUtils.hasText(studentDTO.getSex())){
            sb.append(" and sex = ?");
            args.add(studentDTO.getSex());
        }
        if (StringUtils.hasText(studentDTO.getPhone())){
            sb.append(" and phone like ?");
            args.add("%"+studentDTO.getPhone()+"%");
        }
        if (studentDTO.getBirthStart() != null){
            sb.append(" and birthday >= ?");
            args.add(studentDTO.getBirthStart());
        }
        if (studentDTO.getBirthEnd() != null){
            sb.append(" and birthday < ?");
            args.add(studentDTO.getBirthEnd());
        }

        //查询记录总数
        String countSql = "select count(0) from ("+select+sb.toString()+") t";
        Long count=jdbcTemplate.queryForObject(countSql, Long.class, args.toArray());

        pagination.setTotal(count);

        String limitsql = select+sb.toString()+" limit  ?,?";


        args.add(pagination.getOffset());
        args.add(pagination.getLimit());
        List<Student> studentList = jdbcTemplate.query(limitsql, rowMapper,args.toArray());
        return studentList;
    }

    @Override
    public int deleteByIds(List<Integer> ids) {
        JdbcTemplate jdbcTemplate = Global.getJdbcTemplate();
        if (ids == null || ids.isEmpty()) {
            return -1;
        }
        String sql = "delete from t_hr_humen where id in(";
        StringBuilder sb=new StringBuilder();
        for (int i = 0; i < ids.size(); i++) {
            sb.append("?,");
        }
        sql = sql + sb.substring(0, sb.length() - 1) + ")";
//        sql = sql.substring(0, sql.length() - 1) + ")";
        return jdbcTemplate.update(sql, ids.toArray());
    }

    public int add(Student student) {
        JdbcTemplate jdbcTemplate = Global.getJdbcTemplate();
        String sql = "insert into t_hr_humen(name,sex,phone,email,weixin,card_id,pinyin,birthday) values(?,?,?,?,?,?,?,?)";
        return jdbcTemplate.update(sql, student.getName(), student.getSex(), student.getPhone(), student.getEmail(), student.getWeixin(), student.getCardId(), student.getPinyin(),student.getBirthday()) ;
    }

    public Student findById(Integer id) {
        JdbcTemplate jdbcTemplate = Global.getJdbcTemplate();
        String sql = "select id,name,sex,phone,email,weixin,card_id,pinyin,birthday from t_hr_humen where id = ?";
        return jdbcTemplate.queryForObject(sql, rowMapper, id);
    }

    @Override
    public int edit(Student student) {
        JdbcTemplate jdbcTemplate = Global.getJdbcTemplate();
        String sql = "update t_hr_humen set name = ?,sex = ?,phone = ?,weixin = ?,card_id = ?,pinyin = ?,birthday = ? where id = ?";
        return jdbcTemplate.update(sql, student.getName(), student.getSex(), student.getPhone(), student.getWeixin(), student.getCardId(), student.getPinyin(),student.getBirthday(), student.getId());
    }
}
